﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Xml;
using IDAdmin.Lib.Utils;
using System.Web;
using System.Web.Script.Serialization;
using System.ComponentModel;
using MySql;
using MySql.Data.MySqlClient;
using System.Globalization;
using Cassandra;
using System.Linq;

namespace IDAdmin.Lib.DataLayer
{
    public class WebDB_N
    {
        ///server list List<Server> listServer = ListServer();
        public static List<Server> listServer = ListServer();
        ///key space
        static string KeySpaceCas = "gh_stats";

        //////////////////////////////////////////////////////CCU///////////////////////////////////////////////////
        /// <summary>
        /// Lấy dữ liệu ccu
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<Ccu> getCcu(List<DateTime> dateTimeList)
        {
            List<Ccu> result = new List<Ccu>();
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    DateTime fromDt = dateTimeList[0];
                    DateTime weedday = AddWorkdays(fromDt, -7);
                    DateTime montday = AddWorkdays(fromDt, -30);
                    DateTime yesday = AddWorkdays(fromDt, -1);


                    DateTime toDt = dateTimeList[dateTimeList.Count - 1];
                    Double totime = DataToolUtil.kindDateTimeToKindUnixTimestamp(toDt, DateTimeKind.Local);
                    Double startime = DataToolUtil.kindDateTimeToKindUnixTimestamp(fromDt, DateTimeKind.Local);

                    // ConverterWorkData.truncateTime(ccuItem.Timestamp, "dd/MM/yyyy HH:mm");

                    SqlCommand cmd = new SqlCommand("SELECT timestamp,gamezone,zonename ,onlinemun FROM ccu where CAST(datetime as date) = '" + fromDt.ToString("yyyy-MM-dd") + "'  or CAST(datetime as date) = '" + montday.ToString("yyyy-MM-dd") + "' or CAST(datetime as date) = '" + weedday.ToString("yyyy-MM-dd") + "' or CAST(datetime as date) = '" + yesday.ToString("yyyy-MM-dd") + "'  order by [timestamp] desc", connection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            Ccu ccu = new Ccu();
                            ccu.Timestamp = dataReader["timestamp"].ToString();
                            ccu.GameZone = dataReader["gamezone"].ToString();
                            ccu.ZoneName = dataReader["zonename"].ToString();
                            ccu.Num = dataReader["onlinemun"].ToString();
                            result.Add(ccu);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    {
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        ///get cuu full server
        ///
        public static Dictionary<string, List<Ccu>> getFullCcu(List<DateTime> dateTimeList)
        {
            Dictionary<string, List<Ccu>> result = new Dictionary<string, List<Ccu>>();
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    DateTime fromDt = dateTimeList[0];
                    DateTime weedday = fromDt.AddDays(-7); //AddWorkdays(fromDt, -7);
                    DateTime montday = fromDt.AddDays(-30);  //AddWorkdays(fromDt, -30);
                    DateTime yesday = fromDt.AddDays(-1);  //AddWorkdays(fromDt, -1);


                    DateTime toDt = dateTimeList[dateTimeList.Count - 1];
                    Double totime = DataToolUtil.kindDateTimeToKindUnixTimestamp(toDt, DateTimeKind.Local);
                    Double startime = DataToolUtil.kindDateTimeToKindUnixTimestamp(fromDt, DateTimeKind.Local);
                    
                    foreach (var server in listServer)
                    {
                        SqlCommand cmd = new SqlCommand("SELECT DISTINCT(timestamp),gamezone,zonename,datetime,onlinemun FROM ccu where gamezone=" + server.gamezone + " and ( CAST(datetime as date) = '" + fromDt.ToString("yyyy-MM-dd") + "'  or CAST(datetime as date) = '" + montday.ToString("yyyy-MM-dd") + "' or CAST(datetime as date) = '" + weedday.ToString("yyyy-MM-dd") + "' or CAST(datetime as date) = '" + yesday.ToString("yyyy-MM-dd") + "' ) order by [datetime] asc", connection);
                        List<Ccu> listCCU = new List<Ccu>();
                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                Ccu ccu = new Ccu();
                                ccu.Timestamp = dataReader["timestamp"].ToString();
                                ccu.GameZone = dataReader["gamezone"].ToString();
                                ccu.ZoneName = dataReader["zonename"].ToString();
                                ccu.datetime = Convert.ToDateTime(dataReader["datetime"]);
                                ccu.Num = dataReader["onlinemun"].ToString();
                                listCCU.Add(ccu);
                            }

                            dataReader.Close();
                        }
                        catch (Exception ex)
                        {
                        }
                        result.Add(server.zonename, listCCU);
                    }
                    // ConverterWorkData.truncateTime(ccuItem.Timestamp, "dd/MM/yyyy HH:mm");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        ///get AVG CCU
        ///
        ///get cuu full server
        ///

        public static List<Ccu> getAVGCcu(List<DateTime> dateTimeList)
        {
            List<Ccu> result = new List<Ccu>();
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("select cast(datetime as date) as date,gamezone,AVG(onlinemun)as mun from ccu where datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' and '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59'  group by cast(datetime as date),gamezone order by cast(datetime as date) desc,gamezone desc", connection);

                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            Ccu ccu = new Ccu();
                            ccu.GameZone = dataReader["gamezone"].ToString();
                            ccu.datetime = Convert.ToDateTime(dataReader["date"]);
                            ccu.Num = dataReader["mun"].ToString();
                            result.Add(ccu);
                        }

                        dataReader.Close();
                    }
                    catch (Exception ex)
                    {
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }     

        ///get MAXXX CCU
        ///
        ///
        ///
        public static List<Ccu> getMAxCcu(List<DateTime> dateTimeList)
        {
            List<Ccu> result = new List<Ccu>();
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("select cast(datetime as date) as date,gamezone,MAX(onlinemun)as mun from ccu where datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' and '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59'  group by cast(datetime as date),gamezone order by cast(datetime as date) desc,gamezone desc", connection);
                     
                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                Ccu ccu = new Ccu();
                                ccu.GameZone = dataReader["gamezone"].ToString();
                                ccu.datetime = Convert.ToDateTime(dataReader["date"]);
                                ccu.Num = dataReader["mun"].ToString();
                                result.Add(ccu);
                            }

                            dataReader.Close();
                        }
                        catch (Exception ex)
                        {
                        }                        
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }       

        /// CHECK CUU
        /// 

        public static bool checkCCU(DateTime day)
        {
            try
            {
                List<Ccu> result = new List<Ccu>();
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (var server in listServer)
                    {
                        List<Ccu> result1 = new List<Ccu>();
                       // SqlCommand cmd = new SqlCommand("SELECT DISTINCT(timestamp),gamezone,datetime,zonename ,onlinemun FROM ccu where CAST(datetime as date) BETWEEN '2015-04-14 00:00:00' and '2015-04-14 00:20:00' and gamezone=" + gameServerID + " order by [timestamp] asc", connection);
                        SqlCommand cmd = new SqlCommand("SELECT DISTINCT(timestamp),gamezone,datetime,zonename ,onlinemun FROM ccu where CAST(datetime as date) = '" + day.ToString("yyyy-MM-dd") + "' and gamezone=" + server.gamezone + " order by [timestamp] asc", connection);
                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                Ccu ccu = new Ccu();
                                ccu.Timestamp = dataReader["timestamp"].ToString();
                                ccu.GameZone = dataReader["gamezone"].ToString();
                                ccu.ZoneName = dataReader["zonename"].ToString();
                                ccu.Num = dataReader["onlinemun"].ToString();
                                ccu.datetime = Convert.ToDateTime(dataReader["datetime"]);
                                result.Add(ccu);
                                result1.Add(ccu);
                            }
                            dataReader.Close();
                            DateTime today = day;
                            if (result1.Count < 288)
                            {
                                int i = 0;
                                int j = 0;
                                while (j < 288)
                                {
                                    string a = result1[i].datetime.ToString("yyyy-MM-dd HH:mm");
                                    string b = today.ToString("yyyy-MM-dd HH:mm");
                                    if (a == b)
                                    {
                                        if (i < result1.Count - 1)
                                        {
                                            i++;
                                        }
                                        else
                                        {
                                            i = result1.Count - 1;
                                        }

                                    }
                                    else
                                    {
                                        SqlCommand cmdd = new SqlCommand("spTransfer_Insert_CCU", connection);
                                        cmdd.CommandType = CommandType.StoredProcedure;
                                        cmdd.Parameters.AddWithValue("@onlinemun", "0");
                                        cmdd.Parameters.AddWithValue("@gamezone", server.gamezone);//2491372     row.GetValue<int>("gamezone").ToString());
                                        cmdd.Parameters.AddWithValue("@zonename", "");
                                        cmdd.Parameters.AddWithValue("@timestamp", ConvertToTimestamp(today));
                                        cmdd.Parameters.AddWithValue("@datetime", today);
                                        SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                        pResult.Direction = ParameterDirection.Output;
                                        cmdd.Parameters.Add(pResult);
                                        cmdd.ExecuteNonQuery();
                                    }
                                    today = today.AddMinutes(5);
                                    j++;
                                }

                            }
                            int c = 0;

                        }
                        catch (Exception ex)
                        {
                        }
                    }
                    return true;

                }
            }
            catch (Exception ex)
            {

            }
            return false;

        }
        /// <summary>
        /// check ccu server new
        /// </summary>
        /// <param name="day"></param>
        /// <returns></returns>
        public static bool checkCCUToday(DateTime day)
        {
            try
            {
                DateTime dateNow = DateTime.Now;
                List<Ccu> result = new List<Ccu>();
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (var server in listServer)
                    {
                        List<Ccu> result1 = new List<Ccu>();
                        SqlCommand cmd = new SqlCommand("SELECT DISTINCT(timestamp),gamezone,datetime,zonename ,onlinemun FROM ccu where gamezone=" + server.gamezone + " and CAST(datetime as date) BETWEEN '" + day.ToString("yyyy-MM-dd") + " 00:00:00' and '" + dateNow.ToString("yyyy-MM-dd HH:mm:ss") + "' and gamezone=" + server.gamezone + " order by [timestamp] asc", connection);
                        // SqlCommand cmd = new SqlCommand("SELECT DISTINCT(timestamp),gamezone,datetime,zonename ,onlinemun FROM ccu where CAST(datetime as date) = '" + day.ToString("yyyy-MM-dd") + "' and gamezone=" + gameServerID + " order by [timestamp] asc", connection);
                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                Ccu ccu = new Ccu();
                                ccu.Timestamp = dataReader["timestamp"].ToString();
                                ccu.GameZone = dataReader["gamezone"].ToString();
                                ccu.ZoneName = dataReader["zonename"].ToString();
                                ccu.Num = dataReader["onlinemun"].ToString();
                                ccu.datetime = Convert.ToDateTime(dataReader["datetime"]);
                                result1.Add(ccu);
                                result.Add(ccu);
                            }
                            dataReader.Close();

                            int countTimeAgo = result1.ElementAt(result1.Count - 1).datetime.Hour * 12 + result1.ElementAt(result1.Count - 1).datetime.Minute / 5;
                            DateTime today = day;
                            if (result1.Count <= countTimeAgo)
                            {
                                int i = 0;
                                int j = 0;
                                while (j < countTimeAgo)
                                {
                                    string a = result1[i].datetime.ToString("yyyy-MM-dd HH:mm");
                                    string b = today.ToString("yyyy-MM-dd HH:mm");
                                    if (a == b)
                                    {
                                        if (i < result1.Count - 1)
                                        {
                                            i++;
                                        }
                                        else
                                        {
                                            i = result1.Count - 1;
                                        }

                                    }
                                    else
                                    {
                                        SqlCommand cmdd = new SqlCommand("spTransfer_Insert_CCU", connection);
                                        cmdd.CommandType = CommandType.StoredProcedure;
                                        cmdd.Parameters.AddWithValue("@onlinemun", "0");
                                        cmdd.Parameters.AddWithValue("@gamezone", server.gamezone);//2491372     row.GetValue<int>("gamezone").ToString());
                                        cmdd.Parameters.AddWithValue("@zonename", server.zonename);
                                        cmdd.Parameters.AddWithValue("@timestamp", ConvertToTimestamp(today));
                                        cmdd.Parameters.AddWithValue("@datetime", today);
                                        SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                        pResult.Direction = ParameterDirection.Output;
                                        cmdd.Parameters.Add(pResult);
                                        cmdd.ExecuteNonQuery();
                                    }
                                    today = today.AddMinutes(5);
                                    j++;
                                }

                            }
                            int c = 0;

                        }
                        catch (Exception ex)
                        {
                        }
                    }
                    return true;

                }
            }
            catch (Exception ex)
            {

            }
            return false;

        }
        /// <summary>
        /// insert ccu theo date
        /// </summary>
        static List<int> gameServerListID = new List<int>() { 1004,1006 };
        public static bool InsertCcuCasToSqlserver(string dateTimeList)
        {
            try
            {
                string query = "";
                string Cql = "select * from gh_stats.onlinenum where gamezone={0} and rtimestamp >={1} and rtimestamp <={2} ALLOW FILTERING;";

                DateTime dt = Convert.ToDateTime(dateTimeList);
                string lastday = dt.ToString("MM/dd/yyyy 23:59:59");
                DateTime dt_last = Convert.ToDateTime(lastday);
               // DateTime a = DateTime.Now;
                long totime = ConvertToTimestampT(dt_last) ;
                long startime = ConvertToTimestampT(dt) ;
                using (ISession localSession = ConnectionHelper.getConnectionForCasGhStats())
                {
                    foreach (var server in listServer)
                    {
                        query = String.Format(Cql, server.gamezone + 2490368, startime, totime);
                        RowSet r = localSession.Execute(query);
                        if (r != null)
                        {
                            using (SqlConnection conection = ConnectionHelper.GetConnection())
                            {                         
                                foreach (Row row in r.GetRows())
                                {
                                    DateTime time = UnixTimeStampToDateTime(Convert.ToInt32(row.GetValue<long>("rtimestamp").ToString()));
                                    int m = time.Minute;
                                    if (m % 5 == 0)
                                    {
                                        DateTime date = UnixTimeStampToDateTime(Convert.ToInt32(row.GetValue<long>("rtimestamp").ToString()));
                                        SqlCommand cmd = new SqlCommand("spTransfer_Insert_CCU", conection);
                                        cmd.CommandType = CommandType.StoredProcedure;
                                        cmd.Parameters.AddWithValue("@onlinemun", row.GetValue<int>("onlinenum").ToString());
                                        cmd.Parameters.AddWithValue("@gamezone", server.gamezone);//2491372     row.GetValue<int>("gamezone").ToString());
                                        cmd.Parameters.AddWithValue("@zonename", row.GetValue<string>("zonename").ToString());
                                        cmd.Parameters.AddWithValue("@timestamp", row.GetValue<long>("rtimestamp").ToString());
                                        cmd.Parameters.AddWithValue("@datetime", date);
                                        SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                        pResult.Direction = ParameterDirection.Output;
                                        cmd.Parameters.Add(pResult);
                                        cmd.ExecuteNonQuery();
                                    }
                                }
                            }  
                        }
                    }
                    localSession.Cluster.Shutdown();
                }  
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }
        /// insert all ccu
        /// 
        public static bool InsertALLCcuCasToSqlserver()
        {
            try
            {
                string Cql = "select * from gh_stats.onlinenum where gamezone=2491372;";
                using (ISession localSession = ConnectionHelper.getConnectionForCasGhStats())
                {
                    RowSet r = localSession.Execute(Cql);
                    if (r != null)
                    {
                        // List<Ccu> result = new List<Ccu>();
                        foreach (Row row in r.GetRows())
                        {
                            using (SqlConnection conection = ConnectionHelper.GetConnection())
                            {
                                DateTime time = UnixTimeStampToDateTime(Convert.ToInt32(row.GetValue<long>("rtimestamp").ToString()));
                                int m = time.Minute;
                                if (m % 5 == 0)
                                {
                                    DateTime date = UnixTimeStampToDateTime(Convert.ToInt32(row.GetValue<long>("rtimestamp").ToString()));
                                    SqlCommand cmd = new SqlCommand("spTransfer_Insert_CCU", conection);
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("@onlinemun", row.GetValue<int>("onlinenum").ToString());
                                    cmd.Parameters.AddWithValue("@gamezone", row.GetValue<int>("gamezone").ToString());
                                    cmd.Parameters.AddWithValue("@zonename", row.GetValue<string>("zonename").ToString());
                                    cmd.Parameters.AddWithValue("@timestamp", row.GetValue<long>("rtimestamp").ToString());
                                    cmd.Parameters.AddWithValue("@datetime", date);
                                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                    pResult.Direction = ParameterDirection.Output;
                                    cmd.Parameters.Add(pResult);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                    localSession.Cluster.Shutdown();  
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }
        //////////////////////////////////////////////////////AU///////////////////////////////////////////////////
        
        /// <summary>
        /// get data au
        /// </summary>
        /// <returns></returns>
        /// 

        public static bool InsertAuCasToSqlserver(string day)
        {
            try
            {
                string query = "";
                string Cql = "select * from gh_stats.au where gamezone='{0}' and timestamp>={1} and timestamp <={2} ALLOW FILTERING;";

                DateTime begindayNAU = AddWorkdays(Convert.ToDateTime(day), -1);

                string sD = begindayNAU.ToString("MM/dd/yyyy") + " 00:00:00";
                string eD = begindayNAU.ToString("MM/dd/yyyy") + " 23:59:59";
                DateTime dts = Convert.ToDateTime(sD);
                DateTime dte = Convert.ToDateTime(eD);

                //DateTime dt = Convert.ToDateTime(day);
                //DateTime a = DateTime.Now;
                long totime = ConvertToTimestampT(dte);
                long startime = ConvertToTimestampT(dts);
                using (ISession localSession = ConnectionHelper.getConnectionForCasGhStats())
                {
                    foreach (var server in listServer)
                    {
                        query = String.Format(Cql, server.gamezone, startime, totime);
                        RowSet r = localSession.Execute(query);
                        if (r != null)
                        {
                            using (SqlConnection conection = ConnectionHelper.GetConnection())
                            {

                                foreach (var row in r.GetRows())
                                {
                                    DateTime time = UnixTimeStampToDateTime(Convert.ToInt32(row.GetValue<long>("timestamp")));
                                    SqlCommand cmd = new SqlCommand("spTransfer_Insert_AU", conection); // spTransfer_Insert_AU
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("@account", row.GetValue<string>("account"));
                                    cmd.Parameters.AddWithValue("@gamezone", row.GetValue<string>("gamezone"));
                                    cmd.Parameters.AddWithValue("@ip", row.GetValue<string>("ip"));
                                    cmd.Parameters.AddWithValue("@timestamp", row.GetValue<long>("timestamp"));
                                    cmd.Parameters.AddWithValue("@adatetime", time);
                                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                    pResult.Direction = ParameterDirection.Output;
                                    cmd.Parameters.Add(pResult);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                        localSession.Cluster.Shutdown();
                    }
                    bool b = WebDB_N.InsertStatsAU(day);
                }               
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }

        /// <summary>
        /// get DAU
        /// </summary>
        /// <returns></returns>
        public static int getDAU(string day,int server)
        {
            int result = 0;
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "gamezone="+server+" and " : "";
                    List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                    List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();
                    SqlCommand cmd = new SqlCommand("SELECT COUNT(DISTINCT(account)) as count_acc FROM au where "+ where +" adatetime BETWEEN '" + day + " 00:00:00' AND '" + day + " 23:59:59'", connection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            return Convert.ToInt32(dataReader["count_acc"]);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// get a7
        /// </summary>
        /// <returns></returns>

        public static int getA7(string day,int server)
        {
            int result = 0;
            try
            {
                DateTime dday = Convert.ToDateTime(day);
                DateTime beginday = AddWorkdays(dday, -6);
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "gamezone=" + server + " and " : "";                 
                    SqlCommand cmd = new SqlCommand("select COUNT(DISTINCT(account)) as amount from au where " + where + " adatetime between '" + beginday.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + day + " 23:59:59'", connection);
                    
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            return Convert.ToInt32(dataReader["amount"]);
                                                     
                        }
                        dataReader.Close();
                        // int i = 0;
                        //int a7 = 0;
                        //int count = 0;
                        //int j = 1;
                        //for (int i = 0; i < listAu.Count; i++)
                        //{
                        //    if (j < listAu.Count && listAu[i].Account == listAu[j].Account)
                        //    {
                        //        count++;
                        //        if (count == 7)
                        //        {
                        //            a7++;
                        //            count = 0;
                        //        }
                        //    }
                        //    else
                        //    {
                        //        count = 0;
                        //    }
                        //    j++;
                        //}                        
                        //return a7;
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }


        /// <summary>
        /// get A30
        /// </summary>
        /// <returns></returns>
        /// 
        public static int geta30(string dateTime,int server)
        {
            int result = 0;
            try
            {
                DateTime dday = Convert.ToDateTime(dateTime);
                DateTime beginday = AddWorkdays(dday, -29);

                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "gamezone=" + server + " and " : "";                   
                    SqlCommand cmd = new SqlCommand("select COUNT(DISTINCT(account)) as amount from au where "+where+" adatetime between '" + beginday.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTime + " 23:59:59'", connection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {                        
                            return Convert.ToInt32(dataReader["amount"]);
                        }
                        dataReader.Close();
                        // int i = 0;
                        //int a30 = 0;
                        //int count = 0;
                        //int j = 1;
                        //for (int i = 0; i < listAu.Count; i++){
                        //    if (j < listAu.Count && listAu[i].Account == listAu[j].Account)
                        //    {
                        //        count++;
                        //        if (count == 30)
                        //        {
                        //            a30++;
                        //            count = 0;
                        //        }
                        //    }
                        //    else
                        //    {
                        //        count = 0;
                        //    }
                        //    j++;
                        //}                       
                        //return a30;
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /*
         * get LAU
         *
         */
        public static int getLAU(string dateTime, int server)
        {
            int result = 0;
            try
            {

                DateTime dday = Convert.ToDateTime(dateTime);
                DateTime beginday = AddWorkdays(dday, -7);

                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "gamezone=" + server + " and " : "";
                    SqlCommand cmd = new SqlCommand("select LOWER(account) as account,COUNT(DISTINCT(account)) as amount,CAST(adatetime AS date) AS usetime from au where " + where + " adatetime between '" + beginday.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTime + " 23:59:59' group by CAST(adatetime AS date),account order by account, CAST(adatetime AS date) asc", connection);
                    List<Au> listAu = new List<Au>();
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            Au a = new Au();
                            a.Account = dataReader["account"].ToString();
                            a.Date = dataReader["usetime"].ToString();
                            listAu.Add(a);
                        }
                        dataReader.Close();

                        int lau = 0;
                        //int count = 0;
                        int j = 1;
                        List<string> name = new List<string>();
                        for (int i = 0; i < listAu.Count; i++)
                        {
                            if (j < listAu.Count && listAu[i].Account != listAu[j].Account && listAu[i].Date == beginday.ToString())
                            {
                                name.Add(listAu[i].Account);
                                lau++;
                            }
                            j++;
                        }
                        return lau;
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// Tổng tài khoản đến ngày
        /// 

        public static string GetallAccount(DateTime day,int server)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string sql = "";
                    if (server == 0)
                    {
                        sql = "select COUNT(DISTINCT(account)) as user_online from au where CAST(adatetime as date) <='" + day.ToString("yyyy-MM-dd") + "'";
                    }
                    else
                    {
                        sql = "select COUNT(DISTINCT(account)) as user_online from au where gamezone=" + server + " and CAST(adatetime as date) <='" + day.ToString("yyyy-MM-dd") + "'";
                    }
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                           return dataReader["user_online"].ToString();
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    { }
                }
            }catch(Exception ex){

            }
            return "";
        }

        /// <summary>
        /// tính toán NAU
        /// </summary>
        /// <returns></returns>
        /// 
        public static string InsertNAU(string day) //string day
        {
            string result = "";
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    // SqlCommand cmd = new SqlCommand("select account,adatetime,gamezone from au where adatetime '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' order by adatetime asc ,account");
                     SqlCommand cmd = new SqlCommand("select account,adatetime,gamezone,ip from au  where CAST(adatetime as date)='" + Convert.ToDateTime(day).ToString("yyyy-MM-dd") + "' order by adatetime asc ,account", connection);//SqlCommand cmd = new SqlCommand("select account,adatetime,gamezone,ip from au  where CAST(adatetime as date)='" + Convert.ToDateTime(day).ToString("yyyy-MM-dd") + "' order by adatetime asc ,account", connection);
                    //SqlCommand cmd = new SqlCommand("select account,adatetime,gamezone,ip from au order by adatetime asc ,account", connection);
                    List<Au> aulist = new List<Au>();
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            Au au = new Au();
                            au.Account = dataReader["account"].ToString();
                            au.GameZone = dataReader["gamezone"].ToString();
                            au.IP = dataReader["ip"].ToString();
                            au.DateTime = Convert.ToDateTime(dataReader["adatetime"]);
                            aulist.Add(au);
                        }
                        dataReader.Close();
                        if (aulist != null)
                        {
                            foreach (var item in aulist)
                            {
                                SqlCommand cmdd = new SqlCommand("spTransfer_Insert_NAU", connection);
                                cmdd.CommandType = CommandType.StoredProcedure;
                                cmdd.Parameters.AddWithValue("@account", item.Account);
                                cmdd.Parameters.AddWithValue("@gamezone", item.GameZone);
                                cmdd.Parameters.AddWithValue("@ip", item.IP);
                                cmdd.Parameters.AddWithValue("@datetime", item.DateTime);
                                SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                pResult.Direction = ParameterDirection.Output;

                                cmdd.Parameters.Add(pResult);
                                cmdd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// get NAU
        /// </summary>
        /// <returns></returns>
        /// 
        public static int getNAU(string dateTime,int server)
        {
            int result = 0;
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "gamezone=" + server + " and " : "";
                    List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                    List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();
                    SqlCommand cmd = new SqlCommand("select COUNT(DISTINCT(account)) as amount  from nau where " + where + " datetime between '" + dateTime + " 00:00:00' AND '" + dateTime + " 23:59:59'", connection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            return Convert.ToInt32(dataReader["amount"]);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// get IPLogin
        /// </summary>
        /// <returns></returns>
        /// 
        public static int getIPActive(string dateTime,int server)
        {
            int result = 0;
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "gamezone=" + server + " and " : "";
                    SqlCommand cmd = new SqlCommand("select COUNT(DISTINCT(ip)) as amount  from au where "+where+" adatetime between '" + dateTime + " 00:00:00' AND '" + dateTime + " 23:59:59'", connection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            return Convert.ToInt32(dataReader["amount"]);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// get IPLogin new login
        /// </summary>
        /// <returns></returns>
        /// 
        public static int getIPActiveNew(string dateTime,int server)
        {
            int result = 0;
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "gamezone=" + server + " and " : "";
                    SqlCommand cmd = new SqlCommand("select COUNT(DISTINCT(ip)) as amount  from nau where "+where+" datetime between '" + dateTime + " 00:00:00' AND '" + dateTime + " 23:59:59'", connection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            return Convert.ToInt32(dataReader["amount"]);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// tổng tài khoản thất thoát
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public static int getAlluserlossday(string dateTime,int server)
        {
            int result = 0;

            DateTime dday = Convert.ToDateTime(dateTime);
            DateTime beginday = AddWorkdays(dday, -6);
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "gamezone=" + server + " and " : "";
                    SqlCommand cmd = new SqlCommand("select COUNT(DISTINCT(account)) as user_online from au where "+where+" adatetime between '" + beginday.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTime + " 23:59:59'", connection);
                    int total = 0;
                    int totalNAU = 0;
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            total = Convert.ToInt32(dataReader["user_online"]);
                        }
                        dataReader.Close();

                        SqlCommand cmdd = new SqlCommand("select COUNT(DISTINCT(account)) as amount  from nau where " + where + " cast(datetime as date) <='" + dateTime + "'", connection);
                        SqlDataReader dataReadernaucount = cmdd.ExecuteReader();
                        while (dataReadernaucount.Read())
                        {
                            totalNAU = Convert.ToInt32(dataReadernaucount["amount"]);
                        }
                        dataReadernaucount.Close();
                        return totalNAU - total;
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// số tài khoản thất thoát tăng mới
        /// </summary>
        /// <returns></returns>
        public static int getAlluserlossnew(string dateTime,int server)
        {
            int result = 0;

            DateTime dday = Convert.ToDateTime(dateTime);
            DateTime beginday = AddWorkdays(dday, -6);
            DateTime begindayNAU = AddWorkdays(dday, -7);
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "au.gamezone=" + server + " and NAU.gamezone=" + server + " and" : "";
                    string whereNAU = server != 0 ? "gamezone=" + server + " and" : "";
                    SqlCommand cmd = new SqlCommand("select COUNT(DISTINCT(au.account)) as user_online from au join NAU on au.account = NAU.account where "+where+" adatetime between '" + beginday.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTime + " 23:59:59' and Cast(NAU.datetime as date) = '" + begindayNAU.ToString("yyyy-MM-dd") + "'", connection);
                    int total = 0;
                    int totalNAU = 0;
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();

                        while (dataReader.Read())
                        {
                            total = Convert.ToInt32(dataReader["user_online"]);
                        }
                        dataReader.Close();
                        SqlCommand cmdd = new SqlCommand("select COUNT(DISTINCT(account)) as amount  from nau where " + whereNAU + " datetime between '" + begindayNAU.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + begindayNAU.ToString("yyyy-MM-dd") + " 23:59:59' ", connection);
                        SqlDataReader dataReadernaucount = cmdd.ExecuteReader();
                        while (dataReadernaucount.Read())
                        {
                            totalNAU = Convert.ToInt32(dataReadernaucount["amount"]);
                        }
                        dataReadernaucount.Close();
                        return totalNAU - total;
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }





        /// <summary>
        ///  Insert statsAU
        /// </summary>
        /// <param name="unixTimeStamp"></param>
        /// <returns></returns>
        /// 
        public static bool InsertStatsAU(string day)
        {
            try
            {
                using (SqlConnection conection = ConnectionHelper.GetConnection())
                {
                    InsertNAU(day);
                    DateTime dday = Convert.ToDateTime(day);
                    string beginday = AddWorkdays(dday, -7).ToString("yyyy-MM-dd");

                    List<Server> listS = new List<Server>();// listS = listServer;
                    Server s = new Server();
                    s.gamezone = 0;
                    s.zonename = "";
                    s.tablezone = "";
                    listS.Add(s);
                    foreach (var item in listServer)
                    {
                        listS.Add(item);
                    }
                    foreach (var item in listS)
                    {
                        int totalAcc = Convert.ToInt32(GetallAccount(dday,item.gamezone));
                        int dau = getDAU(day,item.gamezone);
                        int nau = getNAU(day,item.gamezone);
                        int nau7 = getNAU(beginday, item.gamezone);
                        int a7 = getA7(day, item.gamezone);
                        int a30 = geta30(day, item.gamezone);
                        int lau = getLAU(day, item.gamezone);
                        float l7percent = dau != 0 ? (float.Parse(lau.ToString()) / float.Parse(dau.ToString())) * 100 : 0;
                        int alluserlossday = getAlluserlossday(day, item.gamezone);
                        float alluserlossdaypercent = totalAcc != 0 ? (float.Parse(alluserlossday.ToString()) / float.Parse(totalAcc.ToString())) * 100 : 0;
                        int alluserlossnew = getAlluserlossnew(day, item.gamezone);
                        float alluserlossnewpercent = nau7 != 0 ? (float.Parse(alluserlossnew.ToString()) / float.Parse(nau7.ToString())) * 100 : 0;
                        int ipactive = getIPActive(day, item.gamezone);
                        int ipactivenew = getIPActiveNew(day, item.gamezone);
                      //  DateTime d = AddWorkdays(Convert.ToDateTime(day), -7);
                        SqlCommand cmd = DbHelper.CreateCommand("spTransfer_Insert_StatsAu", conection); //spTransfer_Insert_StatsAu
                        cmd.Parameters.AddWithValue("@dau", dau);
                        cmd.Parameters.AddWithValue("@nau", nau);
                        cmd.Parameters.AddWithValue("@a7", a7);
                        cmd.Parameters.AddWithValue("@a30", a30);
                        cmd.Parameters.AddWithValue("@lau", lau);
                        cmd.Parameters.AddWithValue("@l7percent", l7percent);
                        cmd.Parameters.AddWithValue("@alluserlossday", alluserlossday);
                        cmd.Parameters.AddWithValue("@alluserlossdaypercent", alluserlossdaypercent);
                        cmd.Parameters.AddWithValue("@alluserlossnew", alluserlossnew);
                        cmd.Parameters.AddWithValue("@alluserlossnewpercent", alluserlossnewpercent);
                        cmd.Parameters.AddWithValue("@datetime", day);
                        cmd.Parameters.AddWithValue("@dayupdate", beginday); //AddWorkdays(Convert.ToDateTime(day), -7)
                        cmd.Parameters.AddWithValue("@dayupdate30", AddWorkdays(Convert.ToDateTime(day), -30));
                        cmd.Parameters.AddWithValue("@ipactive", ipactive);
                        cmd.Parameters.AddWithValue("@ipactivenew", ipactivenew);
                        cmd.Parameters.AddWithValue("@server", item.gamezone.ToString());
                        SqlParameter paraResult = new SqlParameter("@Result", SqlDbType.Int);
                        paraResult.Direction = ParameterDirection.Output;
                        cmd.Parameters.Add(paraResult);
                        cmd.ExecuteNonQuery();
                    }                   
                    return true;//IDAdmin.Lib.Utils.Converter.ToInt(paraResult.Value) == 1;

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }       

        /// <summary>
        /// get StatsAU
        /// </summary>
        /// <param name="unixTimeStamp"></param>
        /// <returns></returns>
        /// 

        public static List<StatsAu> getStatsAu(List<DateTime> dateTimeList,string server)
        {
            List<StatsAu> result = new List<StatsAu>();
            try
            {

                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("select dau,nau,lau,a7,a30,l7percent,alluserlossday,alluserlossdaypercent,alluserlossnew,alluserlossnewpercent,[datetime],ipactive,ipactivenew from StatsAu where server="+Convert.ToInt32(server)+" and [datetime] between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59' order by [datetime] desc", connection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            StatsAu sA = new StatsAu();
                            sA.dau = dataReader["dau"].ToString();
                            sA.nau = dataReader["nau"].ToString();
                            sA.a7 = dataReader["a7"].ToString();
                            sA.a30 = dataReader["a30"].ToString();
                            sA.lau = dataReader["lau"].ToString();
                            sA.l7percent = dataReader["l7percent"].ToString();
                            sA.alluserlossday = dataReader["alluserlossday"].ToString();
                            sA.alluserlossdaypercent = dataReader["alluserlossdaypercent"].ToString();
                            sA.alluserlossnew = dataReader["alluserlossnew"].ToString();
                            sA.alluserlossnewpercent = dataReader["alluserlossnewpercent"].ToString();
                            sA.datetime = Convert.ToDateTime(dataReader["datetime"]);
                            sA.ipactive = dataReader["ipactive"].ToString();
                            sA.ipactivenew = dataReader["ipactivenew"].ToString();                           
                            result.Add(sA);
                        }
                        dataReader.Close();
                        return result;
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        ///////////////////////////////////////////////GOLD SHOP//////////////////////////////////////////
        /// <summary>
        /// Lấy dữ liệu mua vật phẩm ở gold shop
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        /// 
        public static List<OriginGoldShopData> getItemPurchasingData(List<DateTime> dateTimeList, string shopName,string gamezone,string itemname)
        {

            List<OriginGoldShopData> result = new List<OriginGoldShopData>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {

                    foreach (var server in listServer)
                    {
                        if (server.gamezone == Convert.ToInt32(gamezone) || gamezone == "0")
                        {
                            SqlCommand cmd = new SqlCommand();
                            cmd.Connection = connection;
                            string where = itemname != "" ? "and objname=N'" + itemname + "'" : "";
                            if (shopName.Equals("All") == false)
                            {
                                if (shopName == "Tiệm Tùy Thân")
                                {
                                    cmd.CommandText = "select tmpInfo,baseid,objname,  sum(changes) as mun,COUNT(distinct(dstname)) as mun_buy, pitem.pricegolditem as price,CAST(datetime AS date) AS 'day', action from goldshop" + server.tablezone + " join pitem on pitem.iditem = baseid where type=1 " + where + " and objname NOT IN (N'Gia Tộc Trợ Lệnh',N'Gia Tộc Lệnh',N'Lăng Ba Phù',N'Khinh Công Phù',N'Hồi Thành Quyển') and  action='buy_npc' and " + "tmpInfo=N'" + shopName + "' and datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59' GROUP BY baseid, objname,pricegolditem ,CAST(datetime AS date),tmpInfo,action order by CAST(datetime AS date) desc,tmpInfo";

                                }
                                else
                                {
                                    cmd.CommandText = "select tmpInfo,baseid,objname,  sum(changes) as mun,COUNT(distinct(dstname)) as mun_buy, pitem.pricegolditem as price,CAST(datetime AS date) AS 'day', action from goldshop" + server.tablezone + " join pitem on pitem.iditem = baseid where type=1 " + where + " and " + "tmpInfo=N'" + shopName + "' and datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59' GROUP BY baseid, objname,pricegolditem ,CAST(datetime AS date),tmpInfo,action order by CAST(datetime AS date) desc,tmpInfo";

                                }
                            }
                            else
                            {
                                cmd.CommandText = "select tmpInfo,baseid,objname,  sum(changes) as mun,COUNT(distinct(dstname)) as mun_buy, pitem.pricegolditem as price,CAST(datetime AS date) AS 'day' , action from goldshop" + server.tablezone + " join pitem on pitem.iditem = baseid where type=1 " + where + " and datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59' GROUP BY baseid, objname,pricegolditem ,CAST(datetime AS date),tmpInfo,action order by CAST(datetime AS date) desc,tmpInfo";

                            }
                            try
                            {
                                SqlDataReader dataReader = cmd.ExecuteReader();
                                while (dataReader.Read())
                                {
                                    OriginGoldShopData itPu = new OriginGoldShopData();
                                    itPu.ItemName = dataReader["objname"].ToString();
                                    itPu.BaseId = dataReader["baseid"].ToString();
                                    itPu.TmpInfo = dataReader["tmpInfo"].ToString();

                                    itPu.priceGold = dataReader["price"].ToString();
                                    itPu.MunUserby = dataReader["mun_buy"].ToString();
                                    itPu.TotalGoldSpend = dataReader["mun"].ToString();
                                    itPu.DateTime = Convert.ToDateTime(dataReader["day"].ToString());
                                    itPu.Action = dataReader["action"].ToString();
                                    result.Add(itPu);
                                }
                                dataReader.Close();
                            }
                            catch (Exception ex)
                            { }
                        }
                    }
                    
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static Dictionary<int, List<OriginGoldShopData>> getChangePointGoldshop(List<DateTime> dateTimeList)
        {
            Dictionary<int, List<OriginGoldShopData>> result = new Dictionary<int, List<OriginGoldShopData>>();
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                   
                    foreach (var server in listServer)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                        List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();

                        SqlCommand cmd = new SqlCommand("SELECT SUM(changes) as totalpoint, CAST(datetime AS date) AS 'day' FROM goldshop" + server.tablezone + " WHERE objname=N'Vàng' and srcid=0 and baseid=65534 and type=1 and  action IN (N'Nhấp Thẻ Đổi Vàng',N'㿨ҽ',N'Nạp Thẻ Đổi Vàng',N'Nap The Doi Vang') and datetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' GROUP BY CAST(datetime AS date)", connection);
                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.TotalGoldPoint = !dataReader.IsDBNull(0) ? dataReader["totalpoint"].ToString() : "0";
                                string day = dataReader["day"].ToString();
                                itPu.DateTime = !dataReader.IsDBNull(1) ? Convert.ToDateTime(day) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                r.Add(itPu);
                            }
                            dataReader.Close();
                            int i = 0;
                            foreach (DateTime d in dateTimeList)
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                if (r.Count > i && r[i].DateTime == d)
                                {
                                    itPu = r[i];
                                    i++;
                                }
                                else
                                {
                                    itPu.TotalGoldPoint = "0";
                                    itPu.DateTime = d;

                                }
                                rlast.Add(itPu);
                            }

                        }
                        catch (Exception ex)
                        { }

                        result.Add(server.gamezone, rlast);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }


        public static Dictionary<int, List<OriginGoldShopData>> getTotalGoldSpend(List<DateTime> dateTimeList)
        {
            Dictionary<int, List<OriginGoldShopData>> result = new Dictionary<int, List<OriginGoldShopData>>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                   
                    foreach (var server in listServer)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                        List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();

                        SqlCommand cmd = new SqlCommand("SELECT SUM(changes) AS totalpoint,CAST(datetime AS date) AS 'day' FROM goldshop" + server.tablezone + " where objname=N'Vàng' and type=0 and baseid=65534 and datetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' GROUP BY CAST(datetime AS date)", connection);

                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.TotalGoldSpend = !dataReader.IsDBNull(0) ? dataReader["totalpoint"].ToString() : "0";
                                string day = dataReader["day"].ToString();
                                itPu.DateTime = !dataReader.IsDBNull(1) ? Convert.ToDateTime(day) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                r.Add(itPu);
                            }
                            dataReader.Close();
                            int i = 0;
                            foreach (DateTime d in dateTimeList)
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                if (r.Count > i && r[i].DateTime == d)
                                {
                                    itPu = r[i];
                                    i++;
                                }
                                else
                                {
                                    itPu.TotalGoldPoint = "0";
                                    itPu.DateTime = d;

                                }
                                rlast.Add(itPu);
                            }
                        }
                        catch (Exception ex)
                        { }

                        result.Add(server.gamezone, rlast);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static Dictionary<int, List<OriginGoldShopData>> getTotalGoldSpendSilver(List<DateTime> dateTimeList)
        {
            Dictionary<int, List<OriginGoldShopData>> result = new Dictionary<int, List<OriginGoldShopData>>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                   
                    foreach (var server in listServer)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                        List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();
                        SqlCommand cmd = new SqlCommand("SELECT SUM(changes) AS totalpoint,CAST(datetime AS date) AS 'day' FROM goldshop" + server.tablezone + " where objname=N'Vàng' and type=0 and dstid=0 and dstname='(null)' and action=N'Giao dịch đấu giá vàng' and baseid=65534 and datetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' GROUP BY CAST(datetime AS date)", connection);

                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.TotalGoldSpend = !dataReader.IsDBNull(0) ? dataReader["totalpoint"].ToString() : "0";
                                string day = dataReader["day"].ToString();
                                itPu.DateTime = !dataReader.IsDBNull(1) ? Convert.ToDateTime(day) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                r.Add(itPu);
                            }
                            dataReader.Close();
                            int i = 0;
                            foreach (DateTime d in dateTimeList)
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                if (r.Count > i && r[i].DateTime == d)
                                {
                                    itPu = r[i];
                                    i++;
                                }
                                else
                                {
                                    itPu.TotalGoldPoint = "0";
                                    itPu.DateTime = d;

                                }
                                rlast.Add(itPu);
                            }
                        }
                        catch (Exception ex)
                        { }
                        result.Add(server.gamezone, rlast);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static Dictionary<int, List<OriginGoldShopData>> getTotalGoldChangeSilver(List<DateTime> dateTimeList)
        {
            Dictionary<int, List<OriginGoldShopData>> result = new Dictionary<int, List<OriginGoldShopData>>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                   
                    foreach (var server in listServer)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                        List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();
                        SqlCommand cmd = new SqlCommand("SELECT SUM(changes) AS totalpoint,CAST(datetime AS date) AS 'day' FROM goldshop" + server.tablezone + " where objname='Vàng' and type=0 and dstid=0 and dstname='(null)' and action=N'Vàng đổi Bạc' and baseid=65534 and datetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' GROUP BY CAST(datetime AS date)", connection);

                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.TotalGoldSpend = !dataReader.IsDBNull(0) ? dataReader["totalpoint"].ToString() : "0";
                                string day = dataReader["day"].ToString();
                                itPu.DateTime = !dataReader.IsDBNull(1) ? Convert.ToDateTime(day) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                r.Add(itPu);
                            }
                            dataReader.Close();
                            int i = 0;
                            foreach (DateTime d in dateTimeList)
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                if (r.Count > i && r[i].DateTime == d)
                                {
                                    itPu = r[i];
                                    i++;
                                }
                                else
                                {
                                    itPu.TotalGoldPoint = "0";
                                    itPu.DateTime = d;

                                }
                                rlast.Add(itPu);
                            }
                        }
                        catch (Exception ex)
                        { }

                        result.Add(server.gamezone, rlast);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }



        public static Dictionary<int, List<OriginGoldShopData>> getTotalGoldIBShop(List<DateTime> dateTimeList)
        {
            Dictionary<int, List<OriginGoldShopData>> result = new Dictionary<int, List<OriginGoldShopData>>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    
                    foreach (var server in listServer)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();

                        foreach (DateTime dt in dateTimeList)
                        {

                            SqlCommand cmd = new SqlCommand("select baseid,objname,  sum(changes) as mun,pitem.pricegolditem as price,CAST(datetime AS date) AS 'day' from goldshop" + server.tablezone + " join pitem on pitem.iditem = baseid where type=1 and action='buy_npc' and tmpInfo=N'Tiệm Tùy Thân' and objname NOT IN (N'Gia Tộc Trợ Lệnh',N'Gia Tộc Lệnh',N'Lăng Ba Phù',N'Khinh Công Phù',N'Hồi Thành Quyển') and CAST(datetime AS date)='" + dt.ToString("yyyyMMdd") + "' GROUP BY baseid, objname,pricegolditem ,CAST(datetime AS date)", connection);

                            try
                            {
                                SqlDataReader dataReader = cmd.ExecuteReader();
                                int total = 0;
                                while (dataReader.Read())
                                {
                                    string mun = !dataReader.IsDBNull(0) ? dataReader["mun"].ToString() : "0";
                                    string priceGold = !dataReader.IsDBNull(0) ? dataReader["price"].ToString() : "0";
                                    total += Convert.ToInt32(mun) * Convert.ToInt32(priceGold);
                                }
                                if (!dataReader.Read())
                                {
                                    OriginGoldShopData itPu = new OriginGoldShopData();
                                    itPu.TotalGoldSpend = total.ToString();
                                    itPu.DateStr = dt.ToString("dd/MM/yyyy");
                                    itPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                    // itPu.DateTime = !dataReader.IsDBNull(1) ? DateTime.ParseExact(dataReader["day"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                    r.Add(itPu);
                                }
                                else
                                {
                                    OriginGoldShopData itPu = new OriginGoldShopData();
                                    itPu.TotalGoldSpend = "0";
                                    itPu.DateStr = dt.ToString("dd/MM/yyyy");
                                    itPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                    r.Add(itPu);
                                }
                                dataReader.Close();
                            }
                            catch (Exception ex)
                            { }
                        }
                        result.Add(server.gamezone, r);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// Get goldshop databy 
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>

        public static bool InsertGoldShopDataToSqlserver(List<DateTime> day)
        {
            try
            {
                DateTime dday = day[0];
                DateTime beginday = AddWorkdays(dday, -1);

                string date = beginday.ToString("yyyy-MM-dd");

                string query = "";
                string Cql = "select createid,objid,baseid,objname,tmpinfo,num,changes,type,srcid,dstname,dstid,action,datetime,bind_or_not from gh_stats.goldshop{0} where key =true and datetime>='{1} 00:00:00' and datetime <= '{1} 23:59:59' ALLOW FILTERING;"; //  objname = 'Vàng' and baseid = '65534' 
                using (ISession localSession = ConnectionHelper.getConnectionForCasGhStats())
                {
                    foreach (var server in listServer)
                    {
                        query = String.Format(Cql, server.tablezone, date);
                        RowSet r = localSession.Execute(query);
                        if (r != null)
                        {
                            using (SqlConnection conection = ConnectionHelper.GetConnection())
                            {
                                foreach (Row row in r.GetRows())
                                {
                                    double i = DataToolUtil.kindDateTimeToKindUnixTimestamp(row.GetValue<DateTime>("datetime"), DateTimeKind.Local);
                                    DateTime daten = row.GetValue<DateTime>("datetime");
                                    DateTime a = UnixTimeStampToDateTime(i);
                                    string b = row.GetValue<string>("bind_or_not");
                                    SqlCommand cmd = new SqlCommand("spTransfer_Insert_Goldshop", conection);//spTransfer_Insert_Goldshop
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("@createid", row.GetValue<string>("createid"));
                                    cmd.Parameters.AddWithValue("@objid", row.GetValue<string>("objid"));
                                    cmd.Parameters.AddWithValue("@objname", row.GetValue<string>("objname"));
                                    cmd.Parameters.AddWithValue("@type", row.GetValue<string>("type"));
                                    cmd.Parameters.AddWithValue("@num", row.GetValue<string>("num"));
                                    cmd.Parameters.AddWithValue("@changes", row.GetValue<string>("changes"));
                                    cmd.Parameters.AddWithValue("@srcid", row.GetValue<string>("srcid"));
                                    cmd.Parameters.AddWithValue("@tmpInfo", row.GetValue<string>("tmpinfo"));
                                    cmd.Parameters.AddWithValue("@dstid", row.GetValue<string>("dstid"));
                                    cmd.Parameters.AddWithValue("@dstname", row.GetValue<string>("dstname"));
                                    cmd.Parameters.AddWithValue("@baseid", row.GetValue<string>("baseid"));
                                    cmd.Parameters.AddWithValue("@action", row.GetValue<string>("action"));
                                    cmd.Parameters.AddWithValue("@datetime", UnixTimeStampToDateTime(i));//row.GetValue<DateTime>("datetime")
                                    cmd.Parameters.AddWithValue("@created", DateTime.Now.ToString());
                                    cmd.Parameters.AddWithValue("@bind_or_not", b == "" ? "(null)" : b);
                                    cmd.Parameters.AddWithValue("@tablename", "goldshop" + server.tablezone);
                                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                    pResult.Direction = ParameterDirection.Output;
                                    cmd.Parameters.Add(pResult);
                                    cmd.ExecuteNonQuery();                                   
                                }
                            }
                        }                        
                    }
                    localSession.Cluster.Shutdown();
                }               
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }
        ///Insert Stats comsume
        ///
        public static bool InsertStatsComsume(List<DateTime> dateTimeList)
        {
            try
            {

                Dictionary<int, List<OriginGoldShopData>> dataGoldChangePoint = getChangePointGoldshop(dateTimeList);
                Dictionary<int, List<OriginGoldShopData>> dataGoldSpend = getTotalGoldSpend(dateTimeList);
                Dictionary<int, List<OriginGoldShopData>> dataGoldSpendSilver = getTotalGoldSpendSilver(dateTimeList);
                Dictionary<int, List<OriginGoldShopData>> dataGoldChangeSilver = getTotalGoldChangeSilver(dateTimeList);
                Dictionary<int, List<OriginGoldShopData>> dataGoldIBShop = getTotalGoldIBShop(dateTimeList);

                    using (SqlConnection connection = ConnectionHelper.GetConnection())
                    {
                        int index = 0;
                        foreach (var record in dataGoldSpend.ElementAt(0).Value)
                        {
                            int z = 0;
                            index++;
                            while (z < dataGoldSpend.Count)
                            {
                                SqlCommand cmd = DbHelper.CreateCommand("spTransfer_Insert_StatsComsume", connection);
                                cmd.Parameters.AddWithValue("@totalgoldchange", dataGoldChangePoint.Count != 0 && dataGoldChangePoint.ElementAt(z).Value.Count != 0 && dataGoldChangePoint.ElementAt(z).Value.ElementAt(index - 1).TotalGoldPoint != null ? dataGoldChangePoint.ElementAt(z).Value.ElementAt(index - 1).TotalGoldPoint : "0");
                                cmd.Parameters.AddWithValue("@totalgoldspend", dataGoldSpend.Count != 0 && dataGoldSpend.ElementAt(z).Value.Count != 0 && dataGoldSpend.ElementAt(z).Value.ElementAt(index - 1).TotalGoldSpend != null ? dataGoldSpend.ElementAt(z).Value.ElementAt(index - 1).TotalGoldSpend : "0");
                                cmd.Parameters.AddWithValue("@totalgoldchangesilver", dataGoldChangeSilver.Count != 0 && dataGoldChangeSilver.ElementAt(z).Value.Count != 0 && dataGoldChangeSilver.ElementAt(z).Value.ElementAt(index - 1).TotalGoldSpend != null ? dataGoldChangeSilver.ElementAt(z).Value.ElementAt(index - 1).TotalGoldSpend : "0");
                                cmd.Parameters.AddWithValue("@totalgoldspendsilver", dataGoldSpendSilver.Count != 0 && dataGoldSpendSilver.ElementAt(z).Value.Count != 0 && dataGoldSpendSilver.ElementAt(z).Value.ElementAt(index - 1).TotalGoldSpend != null ? dataGoldSpendSilver.ElementAt(z).Value.ElementAt(index - 1).TotalGoldSpend : "0");
                                cmd.Parameters.AddWithValue("@totalgoldibshop", dataGoldIBShop.Count != 0 && dataGoldIBShop.ElementAt(z).Value.Count != 0 && dataGoldIBShop.ElementAt(z).Value.ElementAt(index - 1).TotalGoldSpend != null ? dataGoldIBShop.ElementAt(z).Value.ElementAt(index - 1).TotalGoldSpend : "0");
                                cmd.Parameters.AddWithValue("@totalmore", "0");
                                cmd.Parameters.AddWithValue("@datetime", record.DateTime);
                                cmd.Parameters.AddWithValue("@server", dataGoldSpend.ElementAt(z).Key);
                                SqlParameter paraResult = new SqlParameter("@Result", SqlDbType.Int);
                                paraResult.Direction = ParameterDirection.Output;
                                cmd.Parameters.Add(paraResult);
                                cmd.ExecuteNonQuery();
                                z++;
                            }
                        }
                    }
                                              

            }
            catch (Exception ex)
            {

            }
            return false;
        }
        /// GEt StatsConsume
        /// 
        public static Dictionary<string, List<StatsComsume>> GetStatsConsume(List<DateTime> dateTimeList)
        {
            Dictionary<string, List<StatsComsume>> result = new Dictionary<string, List<StatsComsume>>();
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {

                    foreach (var server in listServer)
                    {
                        List<StatsComsume> r = new List<StatsComsume>(); ;
                        SqlCommand cmd = new SqlCommand("SELECT totalgoldchange ,totalgoldspend,totalgoldchangesilver,totalgoldspendsilver,totalgoldibshop,totalmore,datetime FROM StatsConsume where server="+server.gamezone+" and datetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' ORDER BY datetime desc", connection);

                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                StatsComsume sC = new StatsComsume();
                                sC.totalgoldchange = dataReader["totalgoldchange"].ToString();
                                sC.totalgoldspend = dataReader["totalgoldspend"].ToString();
                                sC.totalgoldchangesilver = dataReader["totalgoldchangesilver"].ToString();
                                sC.totalgoldspendsilver = dataReader["totalgoldspendsilver"].ToString();
                                sC.totalgoldibshop = dataReader["totalgoldibshop"].ToString();
                                sC.totalmore = dataReader["totalmore"].ToString();
                                sC.datetime =  Convert.ToDateTime(dataReader["datetime"]);
                                r.Add(sC);
                            }
                            dataReader.Close();
                        }
                        catch (Exception ex)
                        { }
                        result.Add(server.zonename, r);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        
        ///////////////////////////////////////////////PCARD//////////////////////////////////////////

        public static bool InsertPcard(DateTime dateTime)
        {
            try
            {

                string query = "";
                string Cql = "SELECT actid,gakey,useuid,usetime as usetime FROM " + KeySpaceCas + ".pcard WHERE keytype=0 and usetime>='{0} 00:00:00' AND usetime<='{0} 23:59:59' ALLOW FILTERING;";                
                using (ISession localSession = ConnectionHelper.getConnectionForCasGhStats())
                {
                    query = String.Format(Cql, dateTime.ToString("yyyy-MM-dd"));
                    RowSet r = localSession.Execute(query);
                    if (r.GetRows() != null)
                    {
                        List<pCard> resultPcard = new List<pCard>();
                        foreach (Row row in r.GetRows())
                        {
                            pCard pC = new pCard();
                            pC.actid = row.GetValue<int>("actid").ToString();
                            pC.useuid = row.GetValue<long>("useuid").ToString();
                            pC.gakey = row.GetValue<string>("gakey");
                            double i = DataToolUtil.kindDateTimeToKindUnixTimestamp(row.GetValue<DateTime>("usetime"), DateTimeKind.Local);
                            string a = UnixTimeStampToDateTime(i).ToString("yyyy-MM-dd");//(DataToolUtil.kindUnixTimeStampToKindDateTime(row.GetValue<DateTime>("usetime")), DateTimeKind.Local, DateTimeKind.Local)).ToString("yyyy-MM-dd");
                            pC.usetime = Convert.ToDateTime(a); //row.GetValue<DateTime>("usetime").ToString("yyyy-MM-dd")
                            resultPcard.Add(pC);
                        }
                        var queryDatapCard = (from pcard in resultPcard
                                              group pcard by new { pcard.actid, pcard.usetime } into g
                                              select new pCard
                                              {
                                                  date = g.Key.usetime.ToString(),
                                                  typeid = g.Key.actid.ToString(),
                                                  count = g.Count()
                                              }).ToList();

                        if (queryDatapCard != null)
                        {
                            using (SqlConnection conection = ConnectionHelper.GetConnection())
                            {
                                foreach (var row in queryDatapCard)
                                {
                                    SqlCommand cmd = new SqlCommand("spTransfer_Insert_Pcard", conection);
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("@usetime", row.date);
                                    cmd.Parameters.AddWithValue("@typeid", row.typeid);
                                    cmd.Parameters.AddWithValue("@count", row.count);
                                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                    pResult.Direction = ParameterDirection.Output;
                                    cmd.Parameters.Add(pResult);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }

                    }
                    localSession.Cluster.Shutdown();   
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }           
            return false;
        }

        /////get Pcard data
        public static List<pCard> getPCardData(List<DateTime> dateTimeList)
        {
            List<pCard> result = new List<pCard>();
            try
            {
                using (SqlConnection conection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("SELECT typeid,count,usetime FROM pcard WHERE usetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' ORDER BY CAST(usetime as date) desc,typeid asc", conection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            pCard pC = new pCard();
                            pC.typeid = !dataReader.IsDBNull(0) ? dataReader["typeid"].ToString() : "0";
                            pC.count = !dataReader.IsDBNull(2) ? Convert.ToInt32(dataReader["count"]) : 0;
                            pC.usetime = Convert.ToDateTime(dataReader["usetime"]);                           
                            result.Add(pC);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    {

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        ///////////////////////////////////////////////Ratio//////////////////////////////////////////
        public static List<RatioGosu> getListRationDb()
        {
            try
            {
                List<RatioGosu> result = new List<RatioGosu>();
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("select id,phieudiem,ratio,gosu from RatioGosu", connection);
                    SqlDataReader dataReader = cmd.ExecuteReader();
                    while (dataReader.Read())
                    {
                        RatioGosu r = new RatioGosu();
                        r.id = (dataReader["id"]).ToString();
                        r.gosu =  Convert.ToInt32(dataReader["gosu"]);
                        r.ratio = dataReader["ratio"].ToString();
                        r.phieudiem = Convert.ToInt32(dataReader["phieudiem"]);
                        result.Add(r);
                    }
                    dataReader.Close();
                }
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static RatioGosu getListRatioBy(int id)
        {
            try
            {
                RatioGosu result = new RatioGosu();
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("select id,phieudiem,ratio,gosu from RatioGosu where id="+id, connection);
                    SqlDataReader dataReader = cmd.ExecuteReader();
                    while (dataReader.Read())
                    {
                        result.id = (dataReader["id"]).ToString();
                        result.gosu = Convert.ToInt32(dataReader["gosu"]);
                        result.ratio = dataReader["ratio"].ToString();
                        result.phieudiem = Convert.ToInt32(dataReader["phieudiem"]);                     
                    }
                    dataReader.Close();
                }
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static bool SaveRatio(RatioGosu r)
        {
            try
            {                
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("sp_Save_Ratio", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@id", r.id);
                    cmd.Parameters.AddWithValue("@gosu", r.gosu);
                    cmd.Parameters.AddWithValue("@ratio", r.ratio);
                    cmd.Parameters.AddWithValue("@phieudiem", r.phieudiem);
                    cmd.ExecuteNonQuery();         
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }

        ///////////////////////////////////////////////MORE FUNCTION//////////////////////////////////////////
        /// <summary>
        /// server list
        /// </summary>
        /// <param name="unixTimeStamp"></param>
        /// <returns></returns>

        public static List<Server> ListServer() {
            List<Server> result = new List<Server>();
            try
            {
                using(SqlConnection connection = ConnectionHelper.GetConnection()){
                    SqlCommand cmd = new SqlCommand("SELECT gamezone,zonename,tablezone,serverphrase,serverphraseName,created FROM server WHERE active=1 order by created desc", connection);
                    SqlDataReader datareader = cmd.ExecuteReader();
                    while (datareader.Read())
                    {
                        Server s = new Server();
                        s.gamezone = Convert.ToInt32(datareader["gamezone"]);
                        s.zonename = datareader["zonename"].ToString();
                        s.tablezone = datareader["tablezone"].ToString();
                        s.serverphrase = datareader["serverphrase"].ToString();
                        s.serverphrasename = datareader["serverphraseName"].ToString();
                        s.created = Convert.ToDateTime(datareader["created"]);
                        result.Add(s);
                    }
                }
                return result;
            }
            catch (Exception ex)
            {

            }
            return result;
        }
        /// <summary>
        /// text time
        /// </summary>
        /// <param name="unixTimeStamp"></param>
        /// <returns></returns>
        public static DateTime UnixTimeStampToDateTime(double unixTimeStamp)
        {
            // Unix timestamp is seconds past epoch
            System.DateTime dtDateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc);
            dtDateTime = dtDateTime.AddSeconds(unixTimeStamp).ToLocalTime();
            return dtDateTime;
        }
        /// <summary>
        /// timestamp to datetime
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        /// 
        private static readonly DateTime Epoch = new DateTime(1970, 1, 1, 0, 0, 0, System.DateTimeKind.Utc);//DateTimeKind.Unspecified
        private static long ConvertToTimestampT(DateTime value)
        {
            TimeSpan elapsedTime = value - Epoch;
            return (long)elapsedTime.TotalSeconds - 25200;
        }
        private static long ConvertToTimestamp(DateTime value)
        {
            long epoch = (value.Ticks - 621355968000000000) / 10000000;
            return epoch;
        }

        /// trừ ngày
        public static DateTime AddWorkdays(DateTime originalDate, int workDays)
        {
            DateTime tmpDate = originalDate;
            int a = workDays > 0 ? 1 : -1;
            while (workDays != 0)
            {
                tmpDate = tmpDate.AddDays(a);
                workDays -= a;
                //workDays--;
            }
            return tmpDate;
        }

        /*
         * get LAU
         *
         */
        public static List<string> getLauList(DateTime date)
        {
            List<string> lauList = new List<string>(); 
            try
            {

               // DateTime dday = Convert.ToDateTime(dateTime);
                DateTime beginday = date.AddDays(-7);

                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                    List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();
                    SqlCommand cmd = new SqlCommand("select DISTINCT(account),COUNT(DISTINCT(account)) as amount,CAST(adatetime AS date) AS usetime from au where adatetime between '" + beginday.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + date.ToString("yyyy-MM-dd") + " 23:59:59' group by CAST(adatetime AS date),account order by account, CAST(adatetime AS date) asc", connection);
                    List<Au> listAu = new List<Au>();
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            Au a = new Au();
                            a.Account = dataReader["account"].ToString();
                            a.Date = dataReader["usetime"].ToString();
                            listAu.Add(a);
                        }
                        dataReader.Close();

                        int lau = 0;
                        //int count = 0;
                        int j = 1;
                        for (int i = 0; i < listAu.Count; i++)
                        {
                            if (j < listAu.Count && listAu[i].Account != listAu[j].Account && listAu[i].Date == beginday.ToString())
                            {
                                lau++;
                                lauList.Add(listAu[i].Account);
                            }
                            j++;
                        }
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lauList;
        }

        ///Test crontab
        ///
        public static bool testcron()
        {
            try
            {        
                string Sql = "insert into charbasevn03 (charid,accid) values (123,456)"; 

                using (SqlConnection conection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand(Sql, conection);        
                    cmd.ExecuteNonQuery();      
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }

        public static List<long> getFill()
        {
            List<long> result = new List<long>();
            string query = "select uid from gh_stats.filledcard;";
            ISession localSession = ConnectionHelper.getConnectionForCasGhStats();
                PreparedStatement state = localSession.Prepare(query);
                BoundStatement boundStatement = new BoundStatement(state);
                RowSet r = localSession.Execute(boundStatement);
                if (r.GetRows() != null)
                {
                    foreach (var row in r.GetRows())
                    {
                       
                        result.Add(row.GetValue<long>("uid"));
                    }
                }
            return result;
        }
        public static Dictionary<long, string> getLevel()
        {
            Dictionary<long, string> result = new Dictionary<long, string>();
            string query = "select accid,name from gh_stats.charbasevn03 where moneycard = 0 and level > 100  ALLOW FILTERING;";
            ISession localSession = ConnectionHelper.getConnectionForCasGhStats();
            PreparedStatement state = localSession.Prepare(query);
            BoundStatement boundStatement = new BoundStatement(state);
            RowSet r = localSession.Execute(boundStatement);
            if (r.GetRows() != null)
            {
                foreach (var row in r.GetRows())
                {
                    result.Add(row.GetValue<long>("accid"), row.GetValue<string>("name"));
                }
            }
            return result;
        }
    }
}
